{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 基于Million Song Dataset的音乐推荐系统-数据处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1 导入工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "#Python SQLITE数据库是一款非常小巧的嵌入式开源数据库软件\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2 读取原始数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#数据集太大，读10000条记录看看\n",
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'train_triplets.txt', \n",
    "                              nrows=10000,sep='\\t', header=None, \n",
    "                              names=['user','song','play_count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAKIMP12A8C130995</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAPDEY12A81C210A9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBBMDR12A8C13253B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFNSP12AF72A0E22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFOVM12A58A7D494</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBNZDC12A6D4FC103</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBSUJE12A6D4F8CF5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBVFZR12A6D4F8AE3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXALG12A8C13C108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXHDL12A81C204C0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  play_count\n",
       "0  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAKIMP12A8C130995           1\n",
       "1  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAPDEY12A81C210A9           1\n",
       "2  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBBMDR12A8C13253B           2\n",
       "3  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFNSP12AF72A0E22           1\n",
       "4  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFOVM12A58A7D494           1\n",
       "5  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBNZDC12A6D4FC103           1\n",
       "6  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBSUJE12A6D4F8CF5           2\n",
       "7  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBVFZR12A6D4F8AE3           1\n",
       "8  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXALG12A8C13C108           1\n",
       "9  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXHDL12A81C204C0           1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3 计算每个用户的总播放次数\n",
    "看哪些用户最活跃（play counts最多） 由于空间有限，不活跃的用户不考虑了（共有约1M用户）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dict = {} #user及对应的play_count次数集合(在所有的歌曲上求和)\n",
    "with open('train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        user = line.split('\\t')[0]            #第一列为用户id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if user in output_dict:\n",
    "            play_count +=output_dict[user]\n",
    "            output_dict.update({user:play_count})\n",
    "        output_dict.update({user:play_count})\n",
    "output_list = [{'user':k,'play_count':v} for k,v in output_dict.items()]\n",
    "play_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "play_count_df = play_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "play_count_df.to_csv(path_or_buf='user_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4 计算每首歌的总播放量\n",
    "看哪些歌曲被播放次数最多（play counts最多） 由于空间有限，不受欢迎的歌曲先不考虑（共有约1M用户）\n",
    "其实两个统计次数一起做亦可（如果机器能存储两个字典）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dict = {}\n",
    "with open('train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        song = line.split('\\t')[1]            #第二列为歌曲id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if song in output_dict:\n",
    "            play_count +=output_dict[song]\n",
    "            output_dict.update({song:play_count})\n",
    "        output_dict.update({song:play_count})\n",
    "output_list = [{'song':k,'play_count':v} for k,v in output_dict.items()]\n",
    "song_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "song_count_df = song_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_count_df.to_csv(path_or_buf='song_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5 得到最活跃的用户和最流行的歌曲"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13132</td>\n",
       "      <td>093cb74eb3c517c5179ae24caf0ebec51b24d2a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9884</td>\n",
       "      <td>119b7c88d58d0c6eb051365c103da5caf817bea6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8210</td>\n",
       "      <td>3fa44653315697f42410a30cb766a4eb102080bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7015</td>\n",
       "      <td>a2679496cd0af9779a92a13ff7c6af5c81ea8c7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6494</td>\n",
       "      <td>d7d2d888ae04d16e994d6964214a1de81392ee04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6472</td>\n",
       "      <td>4ae01afa8f2430ea0704d502bc7b57fb52164882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6150</td>\n",
       "      <td>b7c24f770be6b802805ac0e2106624a517643c17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5656</td>\n",
       "      <td>113255a012b2affeab62607563d03fbdf31b08e7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5620</td>\n",
       "      <td>6d625c6557df84b60d90426c0116138b617b9449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5602</td>\n",
       "      <td>99ac3d883681e21ea68071019dba828ce76fe94d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                                      user\n",
       "0       13132  093cb74eb3c517c5179ae24caf0ebec51b24d2a2\n",
       "1        9884  119b7c88d58d0c6eb051365c103da5caf817bea6\n",
       "2        8210  3fa44653315697f42410a30cb766a4eb102080bb\n",
       "3        7015  a2679496cd0af9779a92a13ff7c6af5c81ea8c7b\n",
       "4        6494  d7d2d888ae04d16e994d6964214a1de81392ee04\n",
       "5        6472  4ae01afa8f2430ea0704d502bc7b57fb52164882\n",
       "6        6150  b7c24f770be6b802805ac0e2106624a517643c17\n",
       "7        5656  113255a012b2affeab62607563d03fbdf31b08e7\n",
       "8        5620  6d625c6557df84b60d90426c0116138b617b9449\n",
       "9        5602  99ac3d883681e21ea68071019dba828ce76fe94d"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "play_count_df = pd.read_csv(filepath_or_buffer='user_playcount_df.csv')\n",
    "play_count_df.head(n =10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>song</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>726885</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648239</td>\n",
       "      <td>SOAUWYT12A81C206F1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>527893</td>\n",
       "      <td>SOSXLTC12AF72A7F54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>425463</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>389880</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>356533</td>\n",
       "      <td>SOAXGDH12A8C13F8A1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>292642</td>\n",
       "      <td>SONYKOW12AB01849C9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>274627</td>\n",
       "      <td>SOPUCYA12A8C13A694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>268353</td>\n",
       "      <td>SOUFTBI12AB0183F65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>244730</td>\n",
       "      <td>SOVDSJC12A58A7A271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                song\n",
       "0      726885  SOBONKR12A58A7A7E0\n",
       "1      648239  SOAUWYT12A81C206F1\n",
       "2      527893  SOSXLTC12AF72A7F54\n",
       "3      425463  SOFRQTD12A81C233C0\n",
       "4      389880  SOEGIYH12A6D4FC0E3\n",
       "5      356533  SOAXGDH12A8C13F8A1\n",
       "6      292642  SONYKOW12AB01849C9\n",
       "7      274627  SOPUCYA12A8C13A694\n",
       "8      268353  SOUFTBI12AB0183F65\n",
       "9      244730  SOVDSJC12A58A7A271"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_count_df = pd.read_csv(filepath_or_buffer='song_playcount_df.csv')\n",
    "song_count_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6 从总体数据中抽取子集\n",
    "前10万个用户 前10万个（10%）用户的播放次数和占总播放次数的40%（2/8原则：20%的用户播放了80%的次数）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_play_count = sum(song_count_df.play_count)\n",
    "(float(play_count_df.head(n=100000).play_count.sum())/total_play_count)*100\n",
    "play_count_subset = play_count_df.head(n=100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "78.39315366645269"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#取3万首歌曲（10%）\n",
    "#前3万首歌曲的播放次数占总播放次数的80%\n",
    "(float(song_count_df.head(n=30000).play_count.sum())/total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_count_subset = song_count_df.head(n=30000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_subset = list(play_count_subset.user)\n",
    "song_subset = list(song_count_subset.song)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'train_triplets.txt',sep='\\t', \n",
    "                              header=None, names=['user','song','play_count'])\n",
    "triplet_dataset_sub = triplet_dataset[triplet_dataset.user.isin(user_subset) ]\n",
    "del(triplet_dataset)\n",
    "triplet_dataset_sub_song = triplet_dataset_sub[triplet_dataset_sub.song.isin(song_subset)]\n",
    "del(triplet_dataset_sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song.to_csv(path_or_buf ='triplet_dataset_sub_song.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10774558, 3)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 处理后还剩余约1千万记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBZZDU12A6310D8A3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOCAHRT12A8C13A1A4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODASIJ12A6D4F5D89</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODEAWL12AB0187032</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         user                song  play_count\n",
       "498  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82          12\n",
       "499  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D           1\n",
       "500  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239           1\n",
       "501  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50           1\n",
       "502  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36           7\n",
       "503  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBONKR12A58A7A7E0          26\n",
       "504  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBZZDU12A6310D8A3           7\n",
       "505  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOCAHRT12A8C13A1A4           5\n",
       "506  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODASIJ12A6D4F5D89           1\n",
       "507  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODEAWL12AB0187032           8"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7 将歌曲元数据与用户播放记录合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('songs',)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect('track_metadata.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT name FROM sqlite_master WHERE type='table'\")\n",
    "cur.fetchall()  #数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "track_metadata_df = pd.read_sql(con=conn, sql='select * from songs')\n",
    "\n",
    "#只保留流行歌曲（3万首）的元数据\n",
    "track_metadata_df_sub = track_metadata_df[track_metadata_df.song_id.isin(song_subset)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>TRMMGCB128E079651D</td>\n",
       "      <td>Get Along (Feat: Pace Won) (Instrumental)</td>\n",
       "      <td>SOHNWIM12A67ADF7D9</td>\n",
       "      <td>Charango</td>\n",
       "      <td>ARU3C671187FB3F71B</td>\n",
       "      <td>067102ea-9519-4622-9077-57ca4164cfbb</td>\n",
       "      <td>Morcheeba</td>\n",
       "      <td>227.47383</td>\n",
       "      <td>0.819087</td>\n",
       "      <td>0.533117</td>\n",
       "      <td>2002</td>\n",
       "      <td>185967</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>TRMMGTX128F92FB4D9</td>\n",
       "      <td>Viejo</td>\n",
       "      <td>SOECFIW12A8C144546</td>\n",
       "      <td>Caraluna</td>\n",
       "      <td>ARPAAPH1187FB3601B</td>\n",
       "      <td>f69d655c-ffd6-4bee-8c2a-3086b2be2fc6</td>\n",
       "      <td>Bacilos</td>\n",
       "      <td>307.51302</td>\n",
       "      <td>0.595554</td>\n",
       "      <td>0.400705</td>\n",
       "      <td>0</td>\n",
       "      <td>6825058</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>TRMMGDP128F933E59A</td>\n",
       "      <td>I Say A Little Prayer</td>\n",
       "      <td>SOGWEOB12AB018A4D0</td>\n",
       "      <td>The Legendary Hi Records Albums_ Volume 3: Ful...</td>\n",
       "      <td>ARNNRN31187B9AE7B7</td>\n",
       "      <td>fb7272ba-f130-4f0a-934d-6eeea4c18c9a</td>\n",
       "      <td>Al Green</td>\n",
       "      <td>133.58975</td>\n",
       "      <td>0.779490</td>\n",
       "      <td>0.599210</td>\n",
       "      <td>1978</td>\n",
       "      <td>5211723</td>\n",
       "      <td>-1</td>\n",
       "      <td>11898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>TRMMHBF12903CF6E59</td>\n",
       "      <td>At the Ball_ That's All</td>\n",
       "      <td>SOJGCRL12A8C144187</td>\n",
       "      <td>Best of Laurel &amp; Hardy - The Lonesome Pine</td>\n",
       "      <td>AR1FEUF1187B9AF3E3</td>\n",
       "      <td>4a8ae4fd-ad6f-4912-851f-093f12ee3572</td>\n",
       "      <td>Laurel &amp; Hardy</td>\n",
       "      <td>123.71546</td>\n",
       "      <td>0.438709</td>\n",
       "      <td>0.307120</td>\n",
       "      <td>0</td>\n",
       "      <td>8645877</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>TRMMHKG12903CDB1B5</td>\n",
       "      <td>Black Gold</td>\n",
       "      <td>SOHNFBA12AB018CD1D</td>\n",
       "      <td>Total Life Forever</td>\n",
       "      <td>ARVXV1J1187FB5BF88</td>\n",
       "      <td>6a65d878-fcd0-42cf-aff9-ca1d636a8bcc</td>\n",
       "      <td>Foals</td>\n",
       "      <td>386.32444</td>\n",
       "      <td>0.842578</td>\n",
       "      <td>0.514523</td>\n",
       "      <td>2010</td>\n",
       "      <td>9007438</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               track_id                                      title  \\\n",
       "115  TRMMGCB128E079651D  Get Along (Feat: Pace Won) (Instrumental)   \n",
       "123  TRMMGTX128F92FB4D9                                      Viejo   \n",
       "145  TRMMGDP128F933E59A                      I Say A Little Prayer   \n",
       "172  TRMMHBF12903CF6E59                    At the Ball_ That's All   \n",
       "191  TRMMHKG12903CDB1B5                                 Black Gold   \n",
       "\n",
       "                song_id                                            release  \\\n",
       "115  SOHNWIM12A67ADF7D9                                           Charango   \n",
       "123  SOECFIW12A8C144546                                           Caraluna   \n",
       "145  SOGWEOB12AB018A4D0  The Legendary Hi Records Albums_ Volume 3: Ful...   \n",
       "172  SOJGCRL12A8C144187         Best of Laurel & Hardy - The Lonesome Pine   \n",
       "191  SOHNFBA12AB018CD1D                                 Total Life Forever   \n",
       "\n",
       "              artist_id                           artist_mbid     artist_name  \\\n",
       "115  ARU3C671187FB3F71B  067102ea-9519-4622-9077-57ca4164cfbb       Morcheeba   \n",
       "123  ARPAAPH1187FB3601B  f69d655c-ffd6-4bee-8c2a-3086b2be2fc6         Bacilos   \n",
       "145  ARNNRN31187B9AE7B7  fb7272ba-f130-4f0a-934d-6eeea4c18c9a        Al Green   \n",
       "172  AR1FEUF1187B9AF3E3  4a8ae4fd-ad6f-4912-851f-093f12ee3572  Laurel & Hardy   \n",
       "191  ARVXV1J1187FB5BF88  6a65d878-fcd0-42cf-aff9-ca1d636a8bcc           Foals   \n",
       "\n",
       "      duration  artist_familiarity  artist_hotttnesss  year  track_7digitalid  \\\n",
       "115  227.47383            0.819087           0.533117  2002            185967   \n",
       "123  307.51302            0.595554           0.400705     0           6825058   \n",
       "145  133.58975            0.779490           0.599210  1978           5211723   \n",
       "172  123.71546            0.438709           0.307120     0           8645877   \n",
       "191  386.32444            0.842578           0.514523  2010           9007438   \n",
       "\n",
       "     shs_perf  shs_work  \n",
       "115        -1         0  \n",
       "123        -1         0  \n",
       "145        -1     11898  \n",
       "172        -1         0  \n",
       "191        -1         0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# python 3 不需要 utf-8 编码\n",
    "# import imp\n",
    "# imp.reload(sys)\n",
    "# sys.setdefaultencoding(\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "track_metadata_df_sub.to_csv(path_or_buf ='track_metadata_df_sub.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30447, 14)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "将合并后的数据进行保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song = pd.read_csv(filepath_or_buffer = 'triplet_dataset_sub_song.csv')\n",
    "track_metadata_df_sub = pd.read_csv(filepath_or_buffer = 'track_metadata_df_sub.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "del(track_metadata_df_sub['track_id'])\n",
    "del(track_metadata_df_sub['artist_mbid'])\n",
    "\n",
    "#去除重复记录\n",
    "track_metadata_df_sub = track_metadata_df_sub.drop_duplicates(['song_id'])\n",
    "\n",
    "#播放历史和歌曲元数据合并\n",
    "triplet_dataset_sub_song_merged = pd.merge(triplet_dataset_sub_song, track_metadata_df_sub, how='left', left_on='song', right_on='song_id')\n",
    "triplet_dataset_sub_song_merged.rename(columns={'play_count':'listen_count'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#去掉一些不用的列\n",
    "del(triplet_dataset_sub_song_merged['song_id'])\n",
    "del(triplet_dataset_sub_song_merged['artist_id'])\n",
    "del(triplet_dataset_sub_song_merged['duration'])\n",
    "del(triplet_dataset_sub_song_merged['artist_familiarity'])\n",
    "del(triplet_dataset_sub_song_merged['artist_hotttnesss'])\n",
    "del(triplet_dataset_sub_song_merged['track_7digitalid'])\n",
    "del(triplet_dataset_sub_song_merged['shs_perf'])\n",
    "del(triplet_dataset_sub_song_merged['shs_work'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "      <td>You And Me Jesus</td>\n",
       "      <td>Tribute To Jake Hess</td>\n",
       "      <td>Jake Hess</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "      <td>Breakfast At Tiffany's</td>\n",
       "      <td>Home</td>\n",
       "      <td>Deep Blue Something</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "      <td>Lucky (Album Version)</td>\n",
       "      <td>We Sing.  We Dance.  We Steal Things.</td>\n",
       "      <td>Jason Mraz &amp; Colbie Caillat</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>26</td>\n",
       "      <td>You're The One</td>\n",
       "      <td>If There Was A Way</td>\n",
       "      <td>Dwight Yoakam</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBZZDU12A6310D8A3</td>\n",
       "      <td>7</td>\n",
       "      <td>Don't Dream It's Over</td>\n",
       "      <td>Recurring Dream_ Best Of Crowded House (Domest...</td>\n",
       "      <td>Crowded House</td>\n",
       "      <td>1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOCAHRT12A8C13A1A4</td>\n",
       "      <td>5</td>\n",
       "      <td>S.O.S.</td>\n",
       "      <td>SOS</td>\n",
       "      <td>Jonas Brothers</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODASIJ12A6D4F5D89</td>\n",
       "      <td>1</td>\n",
       "      <td>The Invisible Man</td>\n",
       "      <td>The Invisible Man</td>\n",
       "      <td>Michael Cretu</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODEAWL12AB0187032</td>\n",
       "      <td>8</td>\n",
       "      <td>American Idiot [feat. Green Day &amp; The Cast Of ...</td>\n",
       "      <td>The Original Broadway Cast Recording 'American...</td>\n",
       "      <td>Green Day</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82            12   \n",
       "1  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D             1   \n",
       "2  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239             1   \n",
       "3  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50             1   \n",
       "4  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36             7   \n",
       "5  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBONKR12A58A7A7E0            26   \n",
       "6  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBZZDU12A6310D8A3             7   \n",
       "7  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOCAHRT12A8C13A1A4             5   \n",
       "8  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODASIJ12A6D4F5D89             1   \n",
       "9  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODEAWL12AB0187032             8   \n",
       "\n",
       "                                               title  \\\n",
       "0                                   You And Me Jesus   \n",
       "1                      Harder Better Faster Stronger   \n",
       "2                                           Uprising   \n",
       "3                             Breakfast At Tiffany's   \n",
       "4                              Lucky (Album Version)   \n",
       "5                                     You're The One   \n",
       "6                              Don't Dream It's Over   \n",
       "7                                             S.O.S.   \n",
       "8                                  The Invisible Man   \n",
       "9  American Idiot [feat. Green Day & The Cast Of ...   \n",
       "\n",
       "                                             release  \\\n",
       "0                               Tribute To Jake Hess   \n",
       "1                                          Discovery   \n",
       "2                                           Uprising   \n",
       "3                                               Home   \n",
       "4              We Sing.  We Dance.  We Steal Things.   \n",
       "5                                 If There Was A Way   \n",
       "6  Recurring Dream_ Best Of Crowded House (Domest...   \n",
       "7                                                SOS   \n",
       "8                                  The Invisible Man   \n",
       "9  The Original Broadway Cast Recording 'American...   \n",
       "\n",
       "                   artist_name  year  \n",
       "0                    Jake Hess  2004  \n",
       "1                    Daft Punk  2007  \n",
       "2                         Muse     0  \n",
       "3          Deep Blue Something  1993  \n",
       "4  Jason Mraz & Colbie Caillat     0  \n",
       "5                Dwight Yoakam  1990  \n",
       "6                Crowded House  1986  \n",
       "7               Jonas Brothers  2007  \n",
       "8                Michael Cretu  1985  \n",
       "9                    Green Day     0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song_merged.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 推荐系统使用的数据集\n",
    "triplet_dataset_sub_song_merged.to_csv(path_or_buf ='triplet_dataset_sub_song_merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9 抽取子集\n",
    "考虑只推荐最流行的5000首歌曲"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('triplet_dataset_sub_song_merged.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_count_df = pd.read_csv('song_playcount_df.csv')\n",
    "song_count_subset = song_count_df.head(n=5000)\n",
    "song_subset = list(song_count_subset.song)\n",
    "data_sub = data[data.song.isin(song_subset)]\n",
    "\n",
    "del data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "      <td>You And Me Jesus</td>\n",
       "      <td>Tribute To Jake Hess</td>\n",
       "      <td>Jake Hess</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "      <td>Breakfast At Tiffany's</td>\n",
       "      <td>Home</td>\n",
       "      <td>Deep Blue Something</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "      <td>Lucky (Album Version)</td>\n",
       "      <td>We Sing.  We Dance.  We Steal Things.</td>\n",
       "      <td>Jason Mraz &amp; Colbie Caillat</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82            12   \n",
       "1  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D             1   \n",
       "2  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239             1   \n",
       "3  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50             1   \n",
       "4  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36             7   \n",
       "\n",
       "                           title                                release  \\\n",
       "0               You And Me Jesus                   Tribute To Jake Hess   \n",
       "1  Harder Better Faster Stronger                              Discovery   \n",
       "2                       Uprising                               Uprising   \n",
       "3         Breakfast At Tiffany's                                   Home   \n",
       "4          Lucky (Album Version)  We Sing.  We Dance.  We Steal Things.   \n",
       "\n",
       "                   artist_name  year  \n",
       "0                    Jake Hess  2004  \n",
       "1                    Daft Punk  2007  \n",
       "2                         Muse     0  \n",
       "3          Deep Blue Something  1993  \n",
       "4  Jason Mraz & Colbie Caillat     0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 5535035 entries, 0 to 10774557\n",
      "Data columns (total 7 columns):\n",
      "user            object\n",
      "song            object\n",
      "listen_count    int64\n",
      "title           object\n",
      "release         object\n",
      "artist_name     object\n",
      "year            int64\n",
      "dtypes: int64(2), object(5)\n",
      "memory usage: 337.8+ MB\n"
     ]
    }
   ],
   "source": [
    "data_sub.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10 对子集数据进行特征编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7127102</th>\n",
       "      <td>093cb74eb3c517c5179ae24caf0ebec51b24d2a2</td>\n",
       "      <td>SOAOSDF12A58A779F1</td>\n",
       "      <td>9667</td>\n",
       "      <td>Corrido de Boxeo</td>\n",
       "      <td>Chavez Ravine</td>\n",
       "      <td>Ry Cooder</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4186520</th>\n",
       "      <td>c11dea7d1f4d227b98c5f2a79561bf76884fcf10</td>\n",
       "      <td>SOZTEZR12A8C14204B</td>\n",
       "      <td>3534</td>\n",
       "      <td>Making Money Off God feat. Bus Driver</td>\n",
       "      <td>B-Boys In Occupied Mexico</td>\n",
       "      <td>2Mex</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2913592</th>\n",
       "      <td>d8e6fa08d73821f305b9a3af1cf1e0a704473d82</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>3532</td>\n",
       "      <td>You're The One</td>\n",
       "      <td>If There Was A Way</td>\n",
       "      <td>Dwight Yoakam</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10752483</th>\n",
       "      <td>1854daf178674bbac9a8ed3d481f95b76676b414</td>\n",
       "      <td>SOVLAWN12A81C234AB</td>\n",
       "      <td>2948</td>\n",
       "      <td>Sweet Escape</td>\n",
       "      <td>Faithful</td>\n",
       "      <td>Paul Cardall</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515742</th>\n",
       "      <td>d13609d62db6df876d3cc388225478618bb7b912</td>\n",
       "      <td>SOFCGSE12AF72A674F</td>\n",
       "      <td>2213</td>\n",
       "      <td>Starshine</td>\n",
       "      <td>Gorillaz</td>\n",
       "      <td>Gorillaz</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9330810</th>\n",
       "      <td>6b36f65d2eb5579a8b9ed5b4731a7e13b8760722</td>\n",
       "      <td>SOGDNAW12A6D4F6804</td>\n",
       "      <td>2165</td>\n",
       "      <td>The Darkest Night Of All</td>\n",
       "      <td>Happiness</td>\n",
       "      <td>Lisa Germano</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9050763</th>\n",
       "      <td>52a6c7b6221f57c89dacbbd06854ca0dc415e9e6</td>\n",
       "      <td>SOWNYYA12A8C139073</td>\n",
       "      <td>1961</td>\n",
       "      <td>Mighty Love                (Chris Willis Album...</td>\n",
       "      <td>Chris Willis</td>\n",
       "      <td>Chris Willis</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8153721</th>\n",
       "      <td>944cdf52364f45b0edd1c972b5a73d3a86b09c6a</td>\n",
       "      <td>SOKGULH12A6D4F70BB</td>\n",
       "      <td>1862</td>\n",
       "      <td>Pepper</td>\n",
       "      <td>Electriclarryland</td>\n",
       "      <td>Butthole Surfers</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7410240</th>\n",
       "      <td>f0b5c784daaa3c7d50acbb3723c7dd649db2b231</td>\n",
       "      <td>SOVCULT12A8C13AAC6</td>\n",
       "      <td>1762</td>\n",
       "      <td>What Do I Do With My Heart</td>\n",
       "      <td>Long Road Out Of Eden</td>\n",
       "      <td>Eagles</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2632248</th>\n",
       "      <td>49127655d27dabab3f469bcdd996330fe4f3e210</td>\n",
       "      <td>SOUNJIO12AB0181339</td>\n",
       "      <td>1729</td>\n",
       "      <td>Gears</td>\n",
       "      <td>Gears</td>\n",
       "      <td>Future Rock</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6665621</th>\n",
       "      <td>1dfea634409fc0c04b90243f752f534c80688cde</td>\n",
       "      <td>SOILOCK12AF72A0116</td>\n",
       "      <td>1666</td>\n",
       "      <td>Indestructible</td>\n",
       "      <td>A Man And His Music</td>\n",
       "      <td>Ray Barretto</td>\n",
       "      <td>1973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9330836</th>\n",
       "      <td>6b36f65d2eb5579a8b9ed5b4731a7e13b8760722</td>\n",
       "      <td>SOMEJEH12A6D4FD320</td>\n",
       "      <td>1646</td>\n",
       "      <td>Now You're Gone</td>\n",
       "      <td>Classic Big Tunes</td>\n",
       "      <td>Basshunter Feat. DJ Mental Theo\u0019s Bazzheadz</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9216718</th>\n",
       "      <td>0d54fad06b250c41865f6af5b8d35dd5c5750c75</td>\n",
       "      <td>SOZPMJT12AAF3B40D1</td>\n",
       "      <td>1620</td>\n",
       "      <td>The Quest</td>\n",
       "      <td>A Taste Of Extreme Divinity</td>\n",
       "      <td>HYPOCRISY</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3947830</th>\n",
       "      <td>ef9fb1992db82a4c6321bc32ac5988083ca83864</td>\n",
       "      <td>SORCVNA12AB01873F8</td>\n",
       "      <td>1608</td>\n",
       "      <td>Down</td>\n",
       "      <td>Played In Space: The Best of Something Corporate</td>\n",
       "      <td>Something Corporate</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10225716</th>\n",
       "      <td>91fc617d2632f554f364015e6e7fb10f3eacce1f</td>\n",
       "      <td>SOMVQNP12A67ADE496</td>\n",
       "      <td>1491</td>\n",
       "      <td>Walkabout</td>\n",
       "      <td>Nothing Changes Under The Sun</td>\n",
       "      <td>Blue States</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8721956</th>\n",
       "      <td>6a6d346cc92ee699a765a06fd97db74b864a8129</td>\n",
       "      <td>SOTWNDJ12A8C143984</td>\n",
       "      <td>1435</td>\n",
       "      <td>Marry Me</td>\n",
       "      <td>Save Me_ San Francisco</td>\n",
       "      <td>Train</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009374</th>\n",
       "      <td>4cc8aeba1a449b566e6680deaffb076a72a3698e</td>\n",
       "      <td>SOQDEGF12A8C13C592</td>\n",
       "      <td>1410</td>\n",
       "      <td>Gotta Get Some</td>\n",
       "      <td>Friday Night</td>\n",
       "      <td>The Spades</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397965</th>\n",
       "      <td>7c9b8125c230d20b19317aa06a4b3f8415057f88</td>\n",
       "      <td>SOTXMEN12A8AE46A2F</td>\n",
       "      <td>1409</td>\n",
       "      <td>Finore</td>\n",
       "      <td>Live In Dublin</td>\n",
       "      <td>Moving Hearts</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3449449</th>\n",
       "      <td>0f5035eac07744d23719c8ba7a537d7eaadb3e71</td>\n",
       "      <td>SOHAYXF12AF72A4382</td>\n",
       "      <td>1336</td>\n",
       "      <td>Letters From The Sky</td>\n",
       "      <td>Civil Twilight</td>\n",
       "      <td>Civil Twilight</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781185</th>\n",
       "      <td>abd1fa850bfabb96ff8ef4a10a7e592188273219</td>\n",
       "      <td>SOPUCYA12A8C13A694</td>\n",
       "      <td>1266</td>\n",
       "      <td>Canada</td>\n",
       "      <td>The End Is Here</td>\n",
       "      <td>Five Iron Frenzy</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7589805</th>\n",
       "      <td>18ef1900567a7ade3b085f6d1c6de4ddcef2f3c8</td>\n",
       "      <td>SOAIOFW12A8C13FAC0</td>\n",
       "      <td>1224</td>\n",
       "      <td>Bad Girlfriend [Explicit]</td>\n",
       "      <td>Scars &amp; Souvenirs</td>\n",
       "      <td>Theory Of A Deadman</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7599528</th>\n",
       "      <td>6d4fd867742b758fb4abe8fef2484feff45f5437</td>\n",
       "      <td>SOAJFTU12A6701E0A3</td>\n",
       "      <td>1217</td>\n",
       "      <td>O My God</td>\n",
       "      <td>Message In A Box</td>\n",
       "      <td>The Police</td>\n",
       "      <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1121546</th>\n",
       "      <td>fe9e24d539f00f7434ab8a7f4fe90dc477d0c4bf</td>\n",
       "      <td>SOVWHPM12AB017DABB</td>\n",
       "      <td>1202</td>\n",
       "      <td>Many Of Horror</td>\n",
       "      <td>Only Revolutions</td>\n",
       "      <td>Biffy Clyro</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10522601</th>\n",
       "      <td>33df533f33efaeba06b3e485ba8f870e132edf1b</td>\n",
       "      <td>SORGUOB12A8C13E30A</td>\n",
       "      <td>1202</td>\n",
       "      <td>Sharecropper's Son</td>\n",
       "      <td>Mountain Tracks: Volume 1</td>\n",
       "      <td>Yonder Mountain String Band</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4724276</th>\n",
       "      <td>0afd55d0e7c895f96adebb221b1d6fc4ad59b23a</td>\n",
       "      <td>SOFPPNM12AB01852A3</td>\n",
       "      <td>1160</td>\n",
       "      <td>Girlfriend (Radio Edit)</td>\n",
       "      <td>Sounds of Vancouver 2010: Closing Ceremony Com...</td>\n",
       "      <td>Avril Lavigne</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1530710</th>\n",
       "      <td>82fed477acd885cc428c29f1423f1929582d0643</td>\n",
       "      <td>SOIPYPB12A8C1360D4</td>\n",
       "      <td>1135</td>\n",
       "      <td>My Immortal (Album Version)</td>\n",
       "      <td>Fallen</td>\n",
       "      <td>Evanescence</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5189521</th>\n",
       "      <td>1a3cebe4f11f71abf431387fb5854423052e93db</td>\n",
       "      <td>SOMYXWV12A8C14232E</td>\n",
       "      <td>1134</td>\n",
       "      <td>What Goes Around...Comes Around</td>\n",
       "      <td>What Goes Around... Comes Around</td>\n",
       "      <td>Justin Timberlake</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4941122</th>\n",
       "      <td>c20bccecc9d79ae108226e36cec975cd3c1227f7</td>\n",
       "      <td>SOMIHYA12AB018DD37</td>\n",
       "      <td>1134</td>\n",
       "      <td>Something Good Can Work</td>\n",
       "      <td>Something Good Can Work</td>\n",
       "      <td>Two Door Cinema Club</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5803563</th>\n",
       "      <td>2ae63e708b3763e4808d355fe962b954048d00ce</td>\n",
       "      <td>SOSXLTC12AF72A7F54</td>\n",
       "      <td>1115</td>\n",
       "      <td>Revelry</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4549232</th>\n",
       "      <td>e504626e4d38404e3928bda4b0f266cbd38c42d8</td>\n",
       "      <td>SOBBGTM12A6D4F6199</td>\n",
       "      <td>1046</td>\n",
       "      <td>A Girl Like You</td>\n",
       "      <td>Empire Records</td>\n",
       "      <td>Edwyn Collins</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148207</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOVWHPM12AB017DABB</td>\n",
       "      <td>1</td>\n",
       "      <td>Many Of Horror</td>\n",
       "      <td>Only Revolutions</td>\n",
       "      <td>Biffy Clyro</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148150</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOLYVAA12AF72A366B</td>\n",
       "      <td>1</td>\n",
       "      <td>Oil And Water</td>\n",
       "      <td>Light Grenades</td>\n",
       "      <td>Incubus</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148211</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOWUTFF12A8C138AB2</td>\n",
       "      <td>1</td>\n",
       "      <td>Frank Sinatra</td>\n",
       "      <td>Fashion Nugget</td>\n",
       "      <td>Cake</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148216</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOXQROF12AB0186B1D</td>\n",
       "      <td>1</td>\n",
       "      <td>The Fear</td>\n",
       "      <td>The Fear</td>\n",
       "      <td>Lily Allen</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285257</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOJSTYO12A8C13F200</td>\n",
       "      <td>1</td>\n",
       "      <td>Breakdown</td>\n",
       "      <td>In Between Dreams</td>\n",
       "      <td>Jack Johnson</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285255</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOJQQDA12AB01843C1</td>\n",
       "      <td>1</td>\n",
       "      <td>Doctor Jones</td>\n",
       "      <td>Greatest Hits</td>\n",
       "      <td>Aqua</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285251</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOIZLKI12A6D4F7B61</td>\n",
       "      <td>1</td>\n",
       "      <td>Supermassive Black Hole (Twilight Soundtrack V...</td>\n",
       "      <td>Music From The Original Motion Picture Soundtrack</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285250</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOIZFTE12AB0186842</td>\n",
       "      <td>1</td>\n",
       "      <td>Fuck Kitty</td>\n",
       "      <td>Frumpie One-Piece</td>\n",
       "      <td>Frumpies</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285247</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOIROON12A6701E0B8</td>\n",
       "      <td>1</td>\n",
       "      <td>Black Hole Sun</td>\n",
       "      <td>True Power-Ballads / 3CD set</td>\n",
       "      <td>Soundgarden</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148155</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOMLMKI12A81C204BC</td>\n",
       "      <td>1</td>\n",
       "      <td>Champion</td>\n",
       "      <td>Graduation</td>\n",
       "      <td>Kanye West</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148146</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOLMARC12A8C13FC61</td>\n",
       "      <td>1</td>\n",
       "      <td>Lost</td>\n",
       "      <td>One Of The Boys</td>\n",
       "      <td>Katy Perry</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285270</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOKWGBI12A6310F1B8</td>\n",
       "      <td>1</td>\n",
       "      <td>Mala Vida</td>\n",
       "      <td>Patchanka</td>\n",
       "      <td>Mano Negra</td>\n",
       "      <td>1988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148111</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOGAUIQ12A6D4F8262</td>\n",
       "      <td>1</td>\n",
       "      <td>Hit The Lights</td>\n",
       "      <td>Some Kind Of Monster</td>\n",
       "      <td>Metallica</td>\n",
       "      <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148079</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOAPNSG12A8C13481A</td>\n",
       "      <td>1</td>\n",
       "      <td>Lemme Get That</td>\n",
       "      <td>Good Girl Gone Bad</td>\n",
       "      <td>Rihanna</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148091</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOCEGJM12A8AE481D3</td>\n",
       "      <td>1</td>\n",
       "      <td>Lazy Eye [Jason Bentley Remix]</td>\n",
       "      <td>Silversun Pickups Remixes</td>\n",
       "      <td>Silversun Pickups</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148092</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOCJHPS12A6D4F8523</td>\n",
       "      <td>1</td>\n",
       "      <td>The Diary Of Jane (Acoustic)</td>\n",
       "      <td>Phobia</td>\n",
       "      <td>Breaking Benjamin</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148094</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOCNYYO12A6D4F910B</td>\n",
       "      <td>1</td>\n",
       "      <td>Conspiracy (Album Version)</td>\n",
       "      <td>All We Know Is Falling</td>\n",
       "      <td>Paramore</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148099</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOCZHGO12A8151DFB7</td>\n",
       "      <td>1</td>\n",
       "      <td>Bigger Boys and Stolen Sweethearts</td>\n",
       "      <td>I Bet You Look Good On The Dance Floor</td>\n",
       "      <td>Arctic Monkeys</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148104</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOECDQZ12A58A784BA</td>\n",
       "      <td>1</td>\n",
       "      <td>Everything Is Alright (Album Version)</td>\n",
       "      <td>Commit This To Memory</td>\n",
       "      <td>Motion City Soundtrack</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148105</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOEFNWL12AF729CD2F</td>\n",
       "      <td>1</td>\n",
       "      <td>Big Munny</td>\n",
       "      <td>One Man Band Man</td>\n",
       "      <td>Swizz Beatz</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148112</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOGFFET12A58A7ECA9</td>\n",
       "      <td>1</td>\n",
       "      <td>Molly's Chambers</td>\n",
       "      <td>Holy Roller Novocaine EP</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148144</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOLDUYB12A67020910</td>\n",
       "      <td>1</td>\n",
       "      <td>When I'm Gone</td>\n",
       "      <td>When I'm Gone Hit Pack</td>\n",
       "      <td>3 Doors Down</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148119</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOGNJVV12A58A7BDE4</td>\n",
       "      <td>1</td>\n",
       "      <td>Calm Before The Storm (Album Version)</td>\n",
       "      <td>Take This To Your Grave</td>\n",
       "      <td>Fall Out Boy</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148129</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOJHFJP12A6D4FD948</td>\n",
       "      <td>1</td>\n",
       "      <td>Forget It</td>\n",
       "      <td>Smallville Volume 2 - Metropolis Mix</td>\n",
       "      <td>Breaking Benjamin</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3285268</th>\n",
       "      <td>d8456e85a2619acbaf916d8fa5fdb79815db2930</td>\n",
       "      <td>SOKUTUM12A6701D9CD</td>\n",
       "      <td>1</td>\n",
       "      <td>Do You Remember</td>\n",
       "      <td>In Between Dreams</td>\n",
       "      <td>Jack Johnson</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148137</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOKGULH12A6D4F70BB</td>\n",
       "      <td>1</td>\n",
       "      <td>Pepper</td>\n",
       "      <td>Electriclarryland</td>\n",
       "      <td>Butthole Surfers</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148138</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOKMPDP12AB017BC0D</td>\n",
       "      <td>1</td>\n",
       "      <td>(If You're Wondering If I Want You To) I Want ...</td>\n",
       "      <td>Raditude</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148139</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOKOTYF12A8C13F2E7</td>\n",
       "      <td>1</td>\n",
       "      <td>Too Far Gone</td>\n",
       "      <td>The All-American Rejects</td>\n",
       "      <td>The All-American Rejects</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7148140</th>\n",
       "      <td>ee7cd9d3d0277237ebb58a3b4b06f441865d4630</td>\n",
       "      <td>SOKQZYZ12A58A7767E</td>\n",
       "      <td>1</td>\n",
       "      <td>Ocean Avenue</td>\n",
       "      <td>Ocean Avenue</td>\n",
       "      <td>Yellowcard</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5401427</th>\n",
       "      <td>20693c71e3a3b90028fa6cb2fd2d467a5e765562</td>\n",
       "      <td>SOPDRWC12A8C141DDE</td>\n",
       "      <td>1</td>\n",
       "      <td>I Want You</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5535035 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              user                song  \\\n",
       "7127102   093cb74eb3c517c5179ae24caf0ebec51b24d2a2  SOAOSDF12A58A779F1   \n",
       "4186520   c11dea7d1f4d227b98c5f2a79561bf76884fcf10  SOZTEZR12A8C14204B   \n",
       "2913592   d8e6fa08d73821f305b9a3af1cf1e0a704473d82  SOBONKR12A58A7A7E0   \n",
       "10752483  1854daf178674bbac9a8ed3d481f95b76676b414  SOVLAWN12A81C234AB   \n",
       "515742    d13609d62db6df876d3cc388225478618bb7b912  SOFCGSE12AF72A674F   \n",
       "9330810   6b36f65d2eb5579a8b9ed5b4731a7e13b8760722  SOGDNAW12A6D4F6804   \n",
       "9050763   52a6c7b6221f57c89dacbbd06854ca0dc415e9e6  SOWNYYA12A8C139073   \n",
       "8153721   944cdf52364f45b0edd1c972b5a73d3a86b09c6a  SOKGULH12A6D4F70BB   \n",
       "7410240   f0b5c784daaa3c7d50acbb3723c7dd649db2b231  SOVCULT12A8C13AAC6   \n",
       "2632248   49127655d27dabab3f469bcdd996330fe4f3e210  SOUNJIO12AB0181339   \n",
       "6665621   1dfea634409fc0c04b90243f752f534c80688cde  SOILOCK12AF72A0116   \n",
       "9330836   6b36f65d2eb5579a8b9ed5b4731a7e13b8760722  SOMEJEH12A6D4FD320   \n",
       "9216718   0d54fad06b250c41865f6af5b8d35dd5c5750c75  SOZPMJT12AAF3B40D1   \n",
       "3947830   ef9fb1992db82a4c6321bc32ac5988083ca83864  SORCVNA12AB01873F8   \n",
       "10225716  91fc617d2632f554f364015e6e7fb10f3eacce1f  SOMVQNP12A67ADE496   \n",
       "8721956   6a6d346cc92ee699a765a06fd97db74b864a8129  SOTWNDJ12A8C143984   \n",
       "2009374   4cc8aeba1a449b566e6680deaffb076a72a3698e  SOQDEGF12A8C13C592   \n",
       "1397965   7c9b8125c230d20b19317aa06a4b3f8415057f88  SOTXMEN12A8AE46A2F   \n",
       "3449449   0f5035eac07744d23719c8ba7a537d7eaadb3e71  SOHAYXF12AF72A4382   \n",
       "2781185   abd1fa850bfabb96ff8ef4a10a7e592188273219  SOPUCYA12A8C13A694   \n",
       "7589805   18ef1900567a7ade3b085f6d1c6de4ddcef2f3c8  SOAIOFW12A8C13FAC0   \n",
       "7599528   6d4fd867742b758fb4abe8fef2484feff45f5437  SOAJFTU12A6701E0A3   \n",
       "1121546   fe9e24d539f00f7434ab8a7f4fe90dc477d0c4bf  SOVWHPM12AB017DABB   \n",
       "10522601  33df533f33efaeba06b3e485ba8f870e132edf1b  SORGUOB12A8C13E30A   \n",
       "4724276   0afd55d0e7c895f96adebb221b1d6fc4ad59b23a  SOFPPNM12AB01852A3   \n",
       "1530710   82fed477acd885cc428c29f1423f1929582d0643  SOIPYPB12A8C1360D4   \n",
       "5189521   1a3cebe4f11f71abf431387fb5854423052e93db  SOMYXWV12A8C14232E   \n",
       "4941122   c20bccecc9d79ae108226e36cec975cd3c1227f7  SOMIHYA12AB018DD37   \n",
       "5803563   2ae63e708b3763e4808d355fe962b954048d00ce  SOSXLTC12AF72A7F54   \n",
       "4549232   e504626e4d38404e3928bda4b0f266cbd38c42d8  SOBBGTM12A6D4F6199   \n",
       "...                                            ...                 ...   \n",
       "7148207   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOVWHPM12AB017DABB   \n",
       "7148150   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOLYVAA12AF72A366B   \n",
       "7148211   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOWUTFF12A8C138AB2   \n",
       "7148216   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOXQROF12AB0186B1D   \n",
       "3285257   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOJSTYO12A8C13F200   \n",
       "3285255   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOJQQDA12AB01843C1   \n",
       "3285251   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOIZLKI12A6D4F7B61   \n",
       "3285250   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOIZFTE12AB0186842   \n",
       "3285247   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOIROON12A6701E0B8   \n",
       "7148155   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOMLMKI12A81C204BC   \n",
       "7148146   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOLMARC12A8C13FC61   \n",
       "3285270   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOKWGBI12A6310F1B8   \n",
       "7148111   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOGAUIQ12A6D4F8262   \n",
       "7148079   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOAPNSG12A8C13481A   \n",
       "7148091   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOCEGJM12A8AE481D3   \n",
       "7148092   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOCJHPS12A6D4F8523   \n",
       "7148094   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOCNYYO12A6D4F910B   \n",
       "7148099   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOCZHGO12A8151DFB7   \n",
       "7148104   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOECDQZ12A58A784BA   \n",
       "7148105   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOEFNWL12AF729CD2F   \n",
       "7148112   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOGFFET12A58A7ECA9   \n",
       "7148144   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOLDUYB12A67020910   \n",
       "7148119   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOGNJVV12A58A7BDE4   \n",
       "7148129   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOJHFJP12A6D4FD948   \n",
       "3285268   d8456e85a2619acbaf916d8fa5fdb79815db2930  SOKUTUM12A6701D9CD   \n",
       "7148137   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOKGULH12A6D4F70BB   \n",
       "7148138   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOKMPDP12AB017BC0D   \n",
       "7148139   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOKOTYF12A8C13F2E7   \n",
       "7148140   ee7cd9d3d0277237ebb58a3b4b06f441865d4630  SOKQZYZ12A58A7767E   \n",
       "5401427   20693c71e3a3b90028fa6cb2fd2d467a5e765562  SOPDRWC12A8C141DDE   \n",
       "\n",
       "          listen_count                                              title  \\\n",
       "7127102           9667                                   Corrido de Boxeo   \n",
       "4186520           3534              Making Money Off God feat. Bus Driver   \n",
       "2913592           3532                                     You're The One   \n",
       "10752483          2948                                       Sweet Escape   \n",
       "515742            2213                                          Starshine   \n",
       "9330810           2165                           The Darkest Night Of All   \n",
       "9050763           1961  Mighty Love                (Chris Willis Album...   \n",
       "8153721           1862                                             Pepper   \n",
       "7410240           1762                         What Do I Do With My Heart   \n",
       "2632248           1729                                              Gears   \n",
       "6665621           1666                                     Indestructible   \n",
       "9330836           1646                                    Now You're Gone   \n",
       "9216718           1620                                          The Quest   \n",
       "3947830           1608                                               Down   \n",
       "10225716          1491                                          Walkabout   \n",
       "8721956           1435                                           Marry Me   \n",
       "2009374           1410                                     Gotta Get Some   \n",
       "1397965           1409                                             Finore   \n",
       "3449449           1336                               Letters From The Sky   \n",
       "2781185           1266                                             Canada   \n",
       "7589805           1224                          Bad Girlfriend [Explicit]   \n",
       "7599528           1217                                           O My God   \n",
       "1121546           1202                                     Many Of Horror   \n",
       "10522601          1202                                 Sharecropper's Son   \n",
       "4724276           1160                            Girlfriend (Radio Edit)   \n",
       "1530710           1135                        My Immortal (Album Version)   \n",
       "5189521           1134                    What Goes Around...Comes Around   \n",
       "4941122           1134                            Something Good Can Work   \n",
       "5803563           1115                                            Revelry   \n",
       "4549232           1046                                    A Girl Like You   \n",
       "...                ...                                                ...   \n",
       "7148207              1                                     Many Of Horror   \n",
       "7148150              1                                      Oil And Water   \n",
       "7148211              1                                      Frank Sinatra   \n",
       "7148216              1                                           The Fear   \n",
       "3285257              1                                          Breakdown   \n",
       "3285255              1                                       Doctor Jones   \n",
       "3285251              1  Supermassive Black Hole (Twilight Soundtrack V...   \n",
       "3285250              1                                         Fuck Kitty   \n",
       "3285247              1                                     Black Hole Sun   \n",
       "7148155              1                                           Champion   \n",
       "7148146              1                                               Lost   \n",
       "3285270              1                                          Mala Vida   \n",
       "7148111              1                                     Hit The Lights   \n",
       "7148079              1                                     Lemme Get That   \n",
       "7148091              1                     Lazy Eye [Jason Bentley Remix]   \n",
       "7148092              1                       The Diary Of Jane (Acoustic)   \n",
       "7148094              1                         Conspiracy (Album Version)   \n",
       "7148099              1                 Bigger Boys and Stolen Sweethearts   \n",
       "7148104              1              Everything Is Alright (Album Version)   \n",
       "7148105              1                                          Big Munny   \n",
       "7148112              1                                   Molly's Chambers   \n",
       "7148144              1                                      When I'm Gone   \n",
       "7148119              1              Calm Before The Storm (Album Version)   \n",
       "7148129              1                                          Forget It   \n",
       "3285268              1                                    Do You Remember   \n",
       "7148137              1                                             Pepper   \n",
       "7148138              1  (If You're Wondering If I Want You To) I Want ...   \n",
       "7148139              1                                       Too Far Gone   \n",
       "7148140              1                                       Ocean Avenue   \n",
       "5401427              1                                         I Want You   \n",
       "\n",
       "                                                    release  \\\n",
       "7127102                                       Chavez Ravine   \n",
       "4186520                           B-Boys In Occupied Mexico   \n",
       "2913592                                  If There Was A Way   \n",
       "10752483                                           Faithful   \n",
       "515742                                             Gorillaz   \n",
       "9330810                                           Happiness   \n",
       "9050763                                        Chris Willis   \n",
       "8153721                                   Electriclarryland   \n",
       "7410240                               Long Road Out Of Eden   \n",
       "2632248                                               Gears   \n",
       "6665621                                 A Man And His Music   \n",
       "9330836                                   Classic Big Tunes   \n",
       "9216718                         A Taste Of Extreme Divinity   \n",
       "3947830    Played In Space: The Best of Something Corporate   \n",
       "10225716                      Nothing Changes Under The Sun   \n",
       "8721956                              Save Me_ San Francisco   \n",
       "2009374                                        Friday Night   \n",
       "1397965                                      Live In Dublin   \n",
       "3449449                                      Civil Twilight   \n",
       "2781185                                     The End Is Here   \n",
       "7589805                                   Scars & Souvenirs   \n",
       "7599528                                    Message In A Box   \n",
       "1121546                                    Only Revolutions   \n",
       "10522601                          Mountain Tracks: Volume 1   \n",
       "4724276   Sounds of Vancouver 2010: Closing Ceremony Com...   \n",
       "1530710                                              Fallen   \n",
       "5189521                    What Goes Around... Comes Around   \n",
       "4941122                             Something Good Can Work   \n",
       "5803563                                   Only By The Night   \n",
       "4549232                                      Empire Records   \n",
       "...                                                     ...   \n",
       "7148207                                    Only Revolutions   \n",
       "7148150                                      Light Grenades   \n",
       "7148211                                      Fashion Nugget   \n",
       "7148216                                            The Fear   \n",
       "3285257                                   In Between Dreams   \n",
       "3285255                                       Greatest Hits   \n",
       "3285251   Music From The Original Motion Picture Soundtrack   \n",
       "3285250                                   Frumpie One-Piece   \n",
       "3285247                        True Power-Ballads / 3CD set   \n",
       "7148155                                          Graduation   \n",
       "7148146                                     One Of The Boys   \n",
       "3285270                                           Patchanka   \n",
       "7148111                                Some Kind Of Monster   \n",
       "7148079                                  Good Girl Gone Bad   \n",
       "7148091                           Silversun Pickups Remixes   \n",
       "7148092                                              Phobia   \n",
       "7148094                              All We Know Is Falling   \n",
       "7148099              I Bet You Look Good On The Dance Floor   \n",
       "7148104                               Commit This To Memory   \n",
       "7148105                                    One Man Band Man   \n",
       "7148112                            Holy Roller Novocaine EP   \n",
       "7148144                              When I'm Gone Hit Pack   \n",
       "7148119                             Take This To Your Grave   \n",
       "7148129                Smallville Volume 2 - Metropolis Mix   \n",
       "3285268                                   In Between Dreams   \n",
       "7148137                                   Electriclarryland   \n",
       "7148138                                            Raditude   \n",
       "7148139                            The All-American Rejects   \n",
       "7148140                                        Ocean Avenue   \n",
       "5401427                                   Only By The Night   \n",
       "\n",
       "                                          artist_name  year  \n",
       "7127102                                     Ry Cooder  2005  \n",
       "4186520                                          2Mex     0  \n",
       "2913592                                 Dwight Yoakam  1990  \n",
       "10752483                                 Paul Cardall  2003  \n",
       "515742                                       Gorillaz  2000  \n",
       "9330810                                  Lisa Germano  1994  \n",
       "9050763                                  Chris Willis     0  \n",
       "8153721                              Butthole Surfers  1996  \n",
       "7410240                                        Eagles  2007  \n",
       "2632248                                   Future Rock     0  \n",
       "6665621                                  Ray Barretto  1973  \n",
       "9330836   Basshunter Feat. DJ Mental Theo\u0019s Bazzheadz  2008  \n",
       "9216718                                     HYPOCRISY  2009  \n",
       "3947830                           Something Corporate  2003  \n",
       "10225716                                  Blue States     0  \n",
       "8721956                                         Train  2009  \n",
       "2009374                                    The Spades     0  \n",
       "1397965                                 Moving Hearts  1992  \n",
       "3449449                                Civil Twilight  2010  \n",
       "2781185                              Five Iron Frenzy     0  \n",
       "7589805                           Theory Of A Deadman  2008  \n",
       "7599528                                    The Police  1983  \n",
       "1121546                                   Biffy Clyro  2009  \n",
       "10522601                  Yonder Mountain String Band  2001  \n",
       "4724276                                 Avril Lavigne  2007  \n",
       "1530710                                   Evanescence  2003  \n",
       "5189521                             Justin Timberlake  2007  \n",
       "4941122                          Two Door Cinema Club  2009  \n",
       "5803563                                 Kings Of Leon  2008  \n",
       "4549232                                 Edwyn Collins  1994  \n",
       "...                                               ...   ...  \n",
       "7148207                                   Biffy Clyro  2009  \n",
       "7148150                                       Incubus  2006  \n",
       "7148211                                          Cake  1996  \n",
       "7148216                                    Lily Allen  2008  \n",
       "3285257                                  Jack Johnson  2005  \n",
       "3285255                                          Aqua     0  \n",
       "3285251                                          Muse     0  \n",
       "3285250                                      Frumpies  1993  \n",
       "3285247                                   Soundgarden  1994  \n",
       "7148155                                    Kanye West  2007  \n",
       "7148146                                    Katy Perry  2007  \n",
       "3285270                                    Mano Negra  1988  \n",
       "7148111                                     Metallica  1982  \n",
       "7148079                                       Rihanna  2007  \n",
       "7148091                             Silversun Pickups     0  \n",
       "7148092                             Breaking Benjamin  2006  \n",
       "7148094                                      Paramore     0  \n",
       "7148099                                Arctic Monkeys  2004  \n",
       "7148104                        Motion City Soundtrack  2005  \n",
       "7148105                                   Swizz Beatz  2007  \n",
       "7148112                                 Kings Of Leon  2003  \n",
       "7148144                                  3 Doors Down  2002  \n",
       "7148119                                  Fall Out Boy  2003  \n",
       "7148129                             Breaking Benjamin  2004  \n",
       "3285268                                  Jack Johnson  2005  \n",
       "7148137                              Butthole Surfers  1996  \n",
       "7148138                                        Weezer  2009  \n",
       "7148139                      The All-American Rejects  2001  \n",
       "7148140                                    Yellowcard  2003  \n",
       "5401427                                 Kings Of Leon  2008  \n",
       "\n",
       "[5535035 rows x 7 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sub.sort_values('listen_count', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对user,song和listen_count，以及year 进行编码\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "user_le = LabelEncoder()\n",
    "user_labels = user_le.fit_transform(data_sub['user'])\n",
    "data_sub['user'] = user_labels\n",
    "\n",
    "song_le = LabelEncoder()\n",
    "song_labels = song_le.fit_transform(data_sub['song'])\n",
    "data_sub['song'] = song_labels\n",
    "\n",
    "# 将播放次数变换到[0,10]区间\n",
    "data_sub['listen_count'] = data_sub['listen_count']/10000\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>83554</td>\n",
       "      <td>26</td>\n",
       "      <td>0.0012</td>\n",
       "      <td>You And Me Jesus</td>\n",
       "      <td>Tribute To Jake Hess</td>\n",
       "      <td>Jake Hess</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>83554</td>\n",
       "      <td>44</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>83554</td>\n",
       "      <td>103</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>83554</td>\n",
       "      <td>187</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Breakfast At Tiffany's</td>\n",
       "      <td>Home</td>\n",
       "      <td>Deep Blue Something</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>83554</td>\n",
       "      <td>303</td>\n",
       "      <td>0.0007</td>\n",
       "      <td>Lucky (Album Version)</td>\n",
       "      <td>We Sing.  We Dance.  We Steal Things.</td>\n",
       "      <td>Jason Mraz &amp; Colbie Caillat</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user  song  listen_count                          title  \\\n",
       "0  83554    26        0.0012               You And Me Jesus   \n",
       "1  83554    44        0.0001  Harder Better Faster Stronger   \n",
       "2  83554   103        0.0001                       Uprising   \n",
       "3  83554   187        0.0001         Breakfast At Tiffany's   \n",
       "4  83554   303        0.0007          Lucky (Album Version)   \n",
       "\n",
       "                                 release                  artist_name  year  \n",
       "0                   Tribute To Jake Hess                    Jake Hess  2004  \n",
       "1                              Discovery                    Daft Punk  2007  \n",
       "2                               Uprising                         Muse     0  \n",
       "3                                   Home          Deep Blue Something  1993  \n",
       "4  We Sing.  We Dance.  We Steal Things.  Jason Mraz & Colbie Caillat     0  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_sub.to_csv(path_or_buf ='triplet_dataset_sub_song_merged_data_sub.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11 用户和歌曲之间的关系处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7007229</th>\n",
       "      <td>99906</td>\n",
       "      <td>3659</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Shoulder to shoulder 2009</td>\n",
       "      <td>Naschi 14 Slov</td>\n",
       "      <td>What We Feel</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007217</th>\n",
       "      <td>99906</td>\n",
       "      <td>1780</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Don't Think I Don't Think About It</td>\n",
       "      <td>Learn To Live</td>\n",
       "      <td>Darius Rucker</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007235</th>\n",
       "      <td>99906</td>\n",
       "      <td>4439</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>She's Everything</td>\n",
       "      <td>Time Well Wasted</td>\n",
       "      <td>Brad Paisley</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007234</th>\n",
       "      <td>99906</td>\n",
       "      <td>4079</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Waitin' On A Woman</td>\n",
       "      <td>Play</td>\n",
       "      <td>Brad Paisley With Andy Griffith</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007233</th>\n",
       "      <td>99906</td>\n",
       "      <td>4033</td>\n",
       "      <td>0.0024</td>\n",
       "      <td>All Night Operator (Part 1)</td>\n",
       "      <td>Ooh La La</td>\n",
       "      <td>Goldfrapp</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007231</th>\n",
       "      <td>99906</td>\n",
       "      <td>3797</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>History In The Making</td>\n",
       "      <td>Learn To Live</td>\n",
       "      <td>Darius Rucker</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007230</th>\n",
       "      <td>99906</td>\n",
       "      <td>3701</td>\n",
       "      <td>0.0010</td>\n",
       "      <td>Revelry</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007228</th>\n",
       "      <td>99906</td>\n",
       "      <td>3595</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>I'm Gonna Miss Her</td>\n",
       "      <td>Part II</td>\n",
       "      <td>Brad Paisley</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007227</th>\n",
       "      <td>99906</td>\n",
       "      <td>3332</td>\n",
       "      <td>0.0035</td>\n",
       "      <td>Yellin' At The Xmas Tree</td>\n",
       "      <td>Devil's Playground</td>\n",
       "      <td>Billy Idol</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007226</th>\n",
       "      <td>99906</td>\n",
       "      <td>3247</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Come A Little Closer</td>\n",
       "      <td>Greatest Hits / Every Mile A Memory 2003 - 2008</td>\n",
       "      <td>Dierks Bentley</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007225</th>\n",
       "      <td>99906</td>\n",
       "      <td>3162</td>\n",
       "      <td>0.0056</td>\n",
       "      <td>Paa vauhtii</td>\n",
       "      <td>Aika ei venaa ketään</td>\n",
       "      <td>Uniikki</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007223</th>\n",
       "      <td>99906</td>\n",
       "      <td>2780</td>\n",
       "      <td>0.0072</td>\n",
       "      <td>Représente</td>\n",
       "      <td>Fat Come Back</td>\n",
       "      <td>Alliance Ethnik</td>\n",
       "      <td>1999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007221</th>\n",
       "      <td>99906</td>\n",
       "      <td>2596</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Rianna</td>\n",
       "      <td>The Update Collection Vol. 2</td>\n",
       "      <td>Fisher</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007220</th>\n",
       "      <td>99906</td>\n",
       "      <td>2357</td>\n",
       "      <td>0.0056</td>\n",
       "      <td>Le Jardin d'Hiver</td>\n",
       "      <td>Smile</td>\n",
       "      <td>Jacky Terrasson</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007222</th>\n",
       "      <td>99906</td>\n",
       "      <td>2716</td>\n",
       "      <td>0.0032</td>\n",
       "      <td>Breed</td>\n",
       "      <td>With The Lights Out - Box Set</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007216</th>\n",
       "      <td>99906</td>\n",
       "      <td>1779</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Alright</td>\n",
       "      <td>Learn To Live</td>\n",
       "      <td>Darius Rucker</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007207</th>\n",
       "      <td>99906</td>\n",
       "      <td>992</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Feel That Fire</td>\n",
       "      <td>Feel That Fire</td>\n",
       "      <td>Dierks Bentley</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007199</th>\n",
       "      <td>99906</td>\n",
       "      <td>168</td>\n",
       "      <td>0.0004</td>\n",
       "      <td>Undo</td>\n",
       "      <td>Vespertine Live</td>\n",
       "      <td>Björk</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007201</th>\n",
       "      <td>99906</td>\n",
       "      <td>307</td>\n",
       "      <td>0.0004</td>\n",
       "      <td>You're The One</td>\n",
       "      <td>If There Was A Way</td>\n",
       "      <td>Dwight Yoakam</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007213</th>\n",
       "      <td>99906</td>\n",
       "      <td>1374</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>Hitsville U.K.</td>\n",
       "      <td>The Singles</td>\n",
       "      <td>The Clash</td>\n",
       "      <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007203</th>\n",
       "      <td>99906</td>\n",
       "      <td>592</td>\n",
       "      <td>0.0004</td>\n",
       "      <td>Walk Of Life</td>\n",
       "      <td>Brothers In Arms</td>\n",
       "      <td>Dire Straits</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007205</th>\n",
       "      <td>99906</td>\n",
       "      <td>759</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Settle For A Slowdown</td>\n",
       "      <td>Modern Day Drifter</td>\n",
       "      <td>Dierks Bentley</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007202</th>\n",
       "      <td>99906</td>\n",
       "      <td>373</td>\n",
       "      <td>0.0025</td>\n",
       "      <td>Greenback Dollar</td>\n",
       "      <td>Money - Greenback Dollar</td>\n",
       "      <td>Kingston Trio</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007208</th>\n",
       "      <td>99906</td>\n",
       "      <td>1058</td>\n",
       "      <td>0.0008</td>\n",
       "      <td>Tive Sim</td>\n",
       "      <td>Nova Bis-Cartola</td>\n",
       "      <td>Cartola</td>\n",
       "      <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007209</th>\n",
       "      <td>99906</td>\n",
       "      <td>1059</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>It Won't Be Like This For Long</td>\n",
       "      <td>Learn To Live</td>\n",
       "      <td>Darius Rucker</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007210</th>\n",
       "      <td>99906</td>\n",
       "      <td>1152</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>I Wanna Make You Close Your Eyes</td>\n",
       "      <td>Feel That Fire</td>\n",
       "      <td>Dierks Bentley</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7007212</th>\n",
       "      <td>99906</td>\n",
       "      <td>1338</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Who's Kissing You Tonight</td>\n",
       "      <td>Relentless</td>\n",
       "      <td>Jason Aldean</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9974939</th>\n",
       "      <td>99905</td>\n",
       "      <td>1101</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>Sehr kosmisch</td>\n",
       "      <td>Musik von Harmonia</td>\n",
       "      <td>Harmonia</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9974961</th>\n",
       "      <td>99905</td>\n",
       "      <td>1766</td>\n",
       "      <td>0.0004</td>\n",
       "      <td>You're A Cad</td>\n",
       "      <td>Ray Guns Are Not Just The Future</td>\n",
       "      <td>the bird and the bee</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9974936</th>\n",
       "      <td>99905</td>\n",
       "      <td>1001</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Nothing Better (Album)</td>\n",
       "      <td>Give Up</td>\n",
       "      <td>Postal Service</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560544</th>\n",
       "      <td>0</td>\n",
       "      <td>1825</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560588</th>\n",
       "      <td>0</td>\n",
       "      <td>4821</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>California Waiting</td>\n",
       "      <td>Youth &amp; Young Manhood</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560559</th>\n",
       "      <td>0</td>\n",
       "      <td>2726</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Gravity</td>\n",
       "      <td>Little Voice</td>\n",
       "      <td>Sara Bareilles</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560560</th>\n",
       "      <td>0</td>\n",
       "      <td>2741</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Say It Ain't So</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560562</th>\n",
       "      <td>0</td>\n",
       "      <td>2855</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>I've Just Seen A Face</td>\n",
       "      <td>Across The Universe</td>\n",
       "      <td>Jim Sturgess</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560563</th>\n",
       "      <td>0</td>\n",
       "      <td>2972</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>I Want You</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560565</th>\n",
       "      <td>0</td>\n",
       "      <td>3112</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>All My Loving</td>\n",
       "      <td>Across The Universe</td>\n",
       "      <td>Jim Sturgess</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560571</th>\n",
       "      <td>0</td>\n",
       "      <td>3647</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>Bubble Toes</td>\n",
       "      <td>Brushfire Fairytales</td>\n",
       "      <td>Jack Johnson</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560572</th>\n",
       "      <td>0</td>\n",
       "      <td>3701</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Revelry</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560573</th>\n",
       "      <td>0</td>\n",
       "      <td>3820</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Love Song</td>\n",
       "      <td>Little Voice</td>\n",
       "      <td>Sara Bareilles</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560577</th>\n",
       "      <td>0</td>\n",
       "      <td>4140</td>\n",
       "      <td>0.0016</td>\n",
       "      <td>Across The Universe</td>\n",
       "      <td>Across The Universe</td>\n",
       "      <td>Jim Sturgess</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560578</th>\n",
       "      <td>0</td>\n",
       "      <td>4390</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>McFearless</td>\n",
       "      <td>Because Of The Times</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560581</th>\n",
       "      <td>0</td>\n",
       "      <td>4579</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>Joe's Head</td>\n",
       "      <td>Youth And Young Manhood</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560586</th>\n",
       "      <td>0</td>\n",
       "      <td>4689</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Be Somebody</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560587</th>\n",
       "      <td>0</td>\n",
       "      <td>4782</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>Just Friends</td>\n",
       "      <td>Back To Black</td>\n",
       "      <td>Amy Winehouse</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560589</th>\n",
       "      <td>0</td>\n",
       "      <td>4851</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Ragoo</td>\n",
       "      <td>Because Of The Times</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560539</th>\n",
       "      <td>0</td>\n",
       "      <td>1532</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Pork And Beans</td>\n",
       "      <td>Pork And Beans</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560591</th>\n",
       "      <td>0</td>\n",
       "      <td>4932</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>He Can Only Hold Her</td>\n",
       "      <td>Back To Black</td>\n",
       "      <td>Amy Winehouse</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560549</th>\n",
       "      <td>0</td>\n",
       "      <td>2227</td>\n",
       "      <td>0.0007</td>\n",
       "      <td>Wrong Turn</td>\n",
       "      <td>Sing-A-Longs &amp; Lullabies For The Film Curious ...</td>\n",
       "      <td>Jack Johnson</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560545</th>\n",
       "      <td>0</td>\n",
       "      <td>1868</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Me &amp; Mr Jones</td>\n",
       "      <td>Me &amp; Mr Jones</td>\n",
       "      <td>Amy Winehouse</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560555</th>\n",
       "      <td>0</td>\n",
       "      <td>2472</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>Hold You In My Arms</td>\n",
       "      <td>Trouble</td>\n",
       "      <td>Ray LaMontagne</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560513</th>\n",
       "      <td>0</td>\n",
       "      <td>256</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Buddy Holly</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560515</th>\n",
       "      <td>0</td>\n",
       "      <td>472</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>Hallelujah</td>\n",
       "      <td>The L Word</td>\n",
       "      <td>Rufus Wainwright</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560516</th>\n",
       "      <td>0</td>\n",
       "      <td>591</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>It Don't Matter</td>\n",
       "      <td>Donavon Frankenreiter</td>\n",
       "      <td>Donavon Frankenreiter</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560518</th>\n",
       "      <td>0</td>\n",
       "      <td>659</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>Cold Desert</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560519</th>\n",
       "      <td>0</td>\n",
       "      <td>698</td>\n",
       "      <td>0.0002</td>\n",
       "      <td>My Name Is Jonas</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>Weezer</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560520</th>\n",
       "      <td>0</td>\n",
       "      <td>723</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>Closer</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560529</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>Molly's Chambers</td>\n",
       "      <td>Holy Roller Novocaine EP</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560531</th>\n",
       "      <td>0</td>\n",
       "      <td>1246</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>Camaro</td>\n",
       "      <td>Because Of The Times</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560592</th>\n",
       "      <td>0</td>\n",
       "      <td>4969</td>\n",
       "      <td>0.0005</td>\n",
       "      <td>A Whiter Shade Of Pale</td>\n",
       "      <td>The Boat That Rocked</td>\n",
       "      <td>Procol Harum</td>\n",
       "      <td>1967</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5535035 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          user  song  listen_count                               title  \\\n",
       "7007229  99906  3659        0.0001           Shoulder to shoulder 2009   \n",
       "7007217  99906  1780        0.0001  Don't Think I Don't Think About It   \n",
       "7007235  99906  4439        0.0001                    She's Everything   \n",
       "7007234  99906  4079        0.0001                  Waitin' On A Woman   \n",
       "7007233  99906  4033        0.0024         All Night Operator (Part 1)   \n",
       "7007231  99906  3797        0.0001               History In The Making   \n",
       "7007230  99906  3701        0.0010                             Revelry   \n",
       "7007228  99906  3595        0.0001                  I'm Gonna Miss Her   \n",
       "7007227  99906  3332        0.0035            Yellin' At The Xmas Tree   \n",
       "7007226  99906  3247        0.0001                Come A Little Closer   \n",
       "7007225  99906  3162        0.0056                         Paa vauhtii   \n",
       "7007223  99906  2780        0.0072                          Représente   \n",
       "7007221  99906  2596        0.0001                              Rianna   \n",
       "7007220  99906  2357        0.0056                   Le Jardin d'Hiver   \n",
       "7007222  99906  2716        0.0032                               Breed   \n",
       "7007216  99906  1779        0.0001                             Alright   \n",
       "7007207  99906   992        0.0001                      Feel That Fire   \n",
       "7007199  99906   168        0.0004                                Undo   \n",
       "7007201  99906   307        0.0004                      You're The One   \n",
       "7007213  99906  1374        0.0020                      Hitsville U.K.   \n",
       "7007203  99906   592        0.0004                        Walk Of Life   \n",
       "7007205  99906   759        0.0001               Settle For A Slowdown   \n",
       "7007202  99906   373        0.0025                    Greenback Dollar   \n",
       "7007208  99906  1058        0.0008                            Tive Sim   \n",
       "7007209  99906  1059        0.0001      It Won't Be Like This For Long   \n",
       "7007210  99906  1152        0.0001    I Wanna Make You Close Your Eyes   \n",
       "7007212  99906  1338        0.0001           Who's Kissing You Tonight   \n",
       "9974939  99905  1101        0.0003                       Sehr kosmisch   \n",
       "9974961  99905  1766        0.0004                        You're A Cad   \n",
       "9974936  99905  1001        0.0001              Nothing Better (Album)   \n",
       "...        ...   ...           ...                                 ...   \n",
       "5560544      0  1825        0.0002                           Manhattan   \n",
       "5560588      0  4821        0.0002                  California Waiting   \n",
       "5560559      0  2726        0.0001                             Gravity   \n",
       "5560560      0  2741        0.0001                     Say It Ain't So   \n",
       "5560562      0  2855        0.0001               I've Just Seen A Face   \n",
       "5560563      0  2972        0.0002                          I Want You   \n",
       "5560565      0  3112        0.0009                       All My Loving   \n",
       "5560571      0  3647        0.0017                         Bubble Toes   \n",
       "5560572      0  3701        0.0001                             Revelry   \n",
       "5560573      0  3820        0.0001                           Love Song   \n",
       "5560577      0  4140        0.0016                 Across The Universe   \n",
       "5560578      0  4390        0.0001                          McFearless   \n",
       "5560581      0  4579        0.0002                          Joe's Head   \n",
       "5560586      0  4689        0.0001                         Be Somebody   \n",
       "5560587      0  4782        0.0002                        Just Friends   \n",
       "5560589      0  4851        0.0001                               Ragoo   \n",
       "5560539      0  1532        0.0001                      Pork And Beans   \n",
       "5560591      0  4932        0.0002                He Can Only Hold Her   \n",
       "5560549      0  2227        0.0007                          Wrong Turn   \n",
       "5560545      0  1868        0.0001                       Me & Mr Jones   \n",
       "5560555      0  2472        0.0002                 Hold You In My Arms   \n",
       "5560513      0   256        0.0001                         Buddy Holly   \n",
       "5560515      0   472        0.0003                          Hallelujah   \n",
       "5560516      0   591        0.0002                     It Don't Matter   \n",
       "5560518      0   659        0.0003                         Cold Desert   \n",
       "5560519      0   698        0.0002                    My Name Is Jonas   \n",
       "5560520      0   723        0.0003                              Closer   \n",
       "5560529      0  1216        0.0001                    Molly's Chambers   \n",
       "5560531      0  1246        0.0003                              Camaro   \n",
       "5560592      0  4969        0.0005              A Whiter Shade Of Pale   \n",
       "\n",
       "                                                   release  \\\n",
       "7007229                                     Naschi 14 Slov   \n",
       "7007217                                      Learn To Live   \n",
       "7007235                                   Time Well Wasted   \n",
       "7007234                                               Play   \n",
       "7007233                                          Ooh La La   \n",
       "7007231                                      Learn To Live   \n",
       "7007230                                  Only By The Night   \n",
       "7007228                                            Part II   \n",
       "7007227                                 Devil's Playground   \n",
       "7007226    Greatest Hits / Every Mile A Memory 2003 - 2008   \n",
       "7007225                               Aika ei venaa ketään   \n",
       "7007223                                      Fat Come Back   \n",
       "7007221                       The Update Collection Vol. 2   \n",
       "7007220                                              Smile   \n",
       "7007222                      With The Lights Out - Box Set   \n",
       "7007216                                      Learn To Live   \n",
       "7007207                                     Feel That Fire   \n",
       "7007199                                    Vespertine Live   \n",
       "7007201                                 If There Was A Way   \n",
       "7007213                                        The Singles   \n",
       "7007203                                   Brothers In Arms   \n",
       "7007205                                 Modern Day Drifter   \n",
       "7007202                           Money - Greenback Dollar   \n",
       "7007208                                   Nova Bis-Cartola   \n",
       "7007209                                      Learn To Live   \n",
       "7007210                                     Feel That Fire   \n",
       "7007212                                         Relentless   \n",
       "9974939                                 Musik von Harmonia   \n",
       "9974961                   Ray Guns Are Not Just The Future   \n",
       "9974936                                            Give Up   \n",
       "...                                                    ...   \n",
       "5560544                                  Only By The Night   \n",
       "5560588                              Youth & Young Manhood   \n",
       "5560559                                       Little Voice   \n",
       "5560560                                             Weezer   \n",
       "5560562                                Across The Universe   \n",
       "5560563                                  Only By The Night   \n",
       "5560565                                Across The Universe   \n",
       "5560571                               Brushfire Fairytales   \n",
       "5560572                                  Only By The Night   \n",
       "5560573                                       Little Voice   \n",
       "5560577                                Across The Universe   \n",
       "5560578                               Because Of The Times   \n",
       "5560581                            Youth And Young Manhood   \n",
       "5560586                                  Only By The Night   \n",
       "5560587                                      Back To Black   \n",
       "5560589                               Because Of The Times   \n",
       "5560539                                     Pork And Beans   \n",
       "5560591                                      Back To Black   \n",
       "5560549  Sing-A-Longs & Lullabies For The Film Curious ...   \n",
       "5560545                                      Me & Mr Jones   \n",
       "5560555                                            Trouble   \n",
       "5560513                                             Weezer   \n",
       "5560515                                         The L Word   \n",
       "5560516                              Donavon Frankenreiter   \n",
       "5560518                                  Only By The Night   \n",
       "5560519                                             Weezer   \n",
       "5560520                                  Only By The Night   \n",
       "5560529                           Holy Roller Novocaine EP   \n",
       "5560531                               Because Of The Times   \n",
       "5560592                               The Boat That Rocked   \n",
       "\n",
       "                             artist_name  year  \n",
       "7007229                     What We Feel     0  \n",
       "7007217                    Darius Rucker  2008  \n",
       "7007235                     Brad Paisley  2005  \n",
       "7007234  Brad Paisley With Andy Griffith  2005  \n",
       "7007233                        Goldfrapp  2005  \n",
       "7007231                    Darius Rucker  2008  \n",
       "7007230                    Kings Of Leon  2008  \n",
       "7007228                     Brad Paisley  2001  \n",
       "7007227                       Billy Idol  2005  \n",
       "7007226                   Dierks Bentley  2005  \n",
       "7007225                          Uniikki  2005  \n",
       "7007223                  Alliance Ethnik  1999  \n",
       "7007221                           Fisher     0  \n",
       "7007220                  Jacky Terrasson  2002  \n",
       "7007222                          Nirvana     0  \n",
       "7007216                    Darius Rucker  2008  \n",
       "7007207                   Dierks Bentley  2008  \n",
       "7007199                            Björk  2001  \n",
       "7007201                    Dwight Yoakam  1990  \n",
       "7007213                        The Clash  1980  \n",
       "7007203                     Dire Straits  1985  \n",
       "7007205                   Dierks Bentley  2005  \n",
       "7007202                    Kingston Trio     0  \n",
       "7007208                          Cartola  1974  \n",
       "7007209                    Darius Rucker  2008  \n",
       "7007210                   Dierks Bentley  2009  \n",
       "7007212                     Jason Aldean  2007  \n",
       "9974939                         Harmonia     0  \n",
       "9974961             the bird and the bee  2009  \n",
       "9974936                   Postal Service  2003  \n",
       "...                                  ...   ...  \n",
       "5560544                    Kings Of Leon  2008  \n",
       "5560588                    Kings Of Leon  2003  \n",
       "5560559                   Sara Bareilles  2004  \n",
       "5560560                           Weezer  1994  \n",
       "5560562                     Jim Sturgess  2007  \n",
       "5560563                    Kings Of Leon  2008  \n",
       "5560565                     Jim Sturgess  2007  \n",
       "5560571                     Jack Johnson  2000  \n",
       "5560572                    Kings Of Leon  2008  \n",
       "5560573                   Sara Bareilles  2007  \n",
       "5560577                     Jim Sturgess  2007  \n",
       "5560578                    Kings Of Leon  2007  \n",
       "5560581                    Kings Of Leon  2003  \n",
       "5560586                    Kings Of Leon  2008  \n",
       "5560587                    Amy Winehouse  2006  \n",
       "5560589                    Kings Of Leon  2007  \n",
       "5560539                           Weezer  2008  \n",
       "5560591                    Amy Winehouse  2006  \n",
       "5560549                     Jack Johnson  2006  \n",
       "5560545                    Amy Winehouse  2006  \n",
       "5560555                   Ray LaMontagne  2004  \n",
       "5560513                           Weezer  1994  \n",
       "5560515                 Rufus Wainwright  2001  \n",
       "5560516            Donavon Frankenreiter  2004  \n",
       "5560518                    Kings Of Leon  2008  \n",
       "5560519                           Weezer  1994  \n",
       "5560520                    Kings Of Leon  2008  \n",
       "5560529                    Kings Of Leon  2003  \n",
       "5560531                    Kings Of Leon  2007  \n",
       "5560592                     Procol Harum  1967  \n",
       "\n",
       "[5535035 rows x 7 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sub.sort_values('user', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 可以得到用户数目为99907个，歌曲为5000首"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入工具包\n",
    "import pickle\n",
    "\n",
    "import itertools\n",
    "\n",
    "#处理事件字符串\n",
    "import datetime\n",
    "\n",
    "import numpy as np\n",
    "import scipy.io as sio\n",
    "import scipy.sparse as ss\n",
    "\n",
    "from collections import defaultdict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_Users = 99907\n",
    "n_Songs = 5000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "#统计每个用户打过份的电影   / 每个电影被哪些用户打过分\n",
    "SongsForUser = defaultdict(set)\n",
    "UsersForSong = defaultdict(set)\n",
    "    \n",
    "for filename in [\"triplet_dataset_sub_song_merged_data_sub.csv\"]:\n",
    "    #user id | song id | listen_count | title | release | artist_name | year\n",
    "    for line in open(filename,'r'):  #对每条记录\n",
    "        (user,song,listen_count,title,release,artist_name,year)=line.split(',')\n",
    "\n",
    "        #倒排表\n",
    "        SongsForUser[user].add(song)    #该用户听了这首歌\n",
    "        UsersForSong[song].add(user)    #该歌曲被用户听了\n",
    "\n",
    "##统计每个用户听的歌曲\n",
    "pickle.dump(SongsForUser, open(\"SongsForUser.pkl\", 'wb'))\n",
    "##统计听了该歌曲的用户\n",
    "pickle.dump(UsersForSong, open(\"UsersForSong.pkl\", 'wb'))\n",
    "\n",
    "#用户-歌曲关系矩阵表（打分），可用于后续LFM/SVD++处理的输入\n",
    "#这是一个稀疏矩阵，记录用户对每首歌听的次数\n",
    "UserSongScores = ss.dok_matrix((n_Users, n_Songs))\n",
    "    \n",
    "\n",
    "\n",
    "# 为了防止不必要的计算，我们找出来所有关联的用户 或者 关联的歌曲\n",
    "\n",
    "uniqueUserPairs = set()\n",
    "uniqueSongPairs = set()\n",
    "for song in range(n_Songs):\n",
    "    users = UsersForSong[song]\n",
    "    if len(users) > 2:\n",
    "        uniqueUserPairs.update(itertools.combinations(users, 2))\n",
    "        \n",
    "for user in range(n_Users):\n",
    "    songs = SongsForUser[user]\n",
    "    if len(songs) > 2:\n",
    "        uniqueMoviePairs.update(itertools.combinations(songs, 2))\n",
    " \n",
    "#保存用户-事件关系对索引表\n",
    "pickle.dump(uniqueUserPairs, open(\"uniqueUserPairs.pkl\", 'wb'))\n",
    "pickle.dump(uniqueSongPairs, open(\"uniqueSongPairs.pkl\", 'wb'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
